/*
 * DB.java
 * 1.0
 * 2010/09/12
 */
package RoomNanny;
import java.sql.*;
import java.util.HashMap;
import java.util.HashSet;

/**
 * Class container for the static API functions exposed to 
 * the Embedded Systems team.
 * 
 * @author Doug Hamilton
 * @version 1.1
 */
public class DB {
	
	private final static String USERNAME = "ES_API";
	private final static String PASSWORD = "allmybase";
	private static boolean debugMode = false;
	private static Connection connection;
	private static CallableStatement sensorCall;
	private static CallableStatement dataCall;
	private static HashSet<Integer> sensorIDs = new HashSet<Integer>();
	private static HashMap<DoorState, String> doorStateMapping = new HashMap<DoorState, String>();
	// Althrough lights are on or off, we're accommodating other possibilities by using a mapping
	private static HashMap<Boolean, String> lightStateMapping = new HashMap<Boolean, String>();
	
	// Static block to construct state mappings
	static {
		doorStateMapping.put(DoorState.Opening, "opening");
		doorStateMapping.put(DoorState.Closing, "closing");
		doorStateMapping.put(DoorState.Moving, "moving");
		doorStateMapping.put(DoorState.Still, "still");
		lightStateMapping.put(false, "off");
		lightStateMapping.put(true, "on");
	}
	/**
	 * Registers a sensor's hardware into the Room Nanny database.  Returns an integer representing the ID for the sensor.
	 * <b>Required for each sensor pushing data</b>.  DB.connect must be called <b>before</b> any registerSensor calls.
	 * @param sensorID Serial number of the sensor in String format. Ex: 1234.1234.1234.1234
	 * @return The ID of the sensor that should be passed into the pushData calls.
	 * @throws DBException If an error occurs while registering the sensor
	 * @see DB#connect(String, boolean)
	 */
	public static int registerSensor(String sensorID) throws DBException {
		
		try {
			if(connection == null || connection.isClosed()) {
				throw new DBException("Not connected to Room Nanny database. Please call RoomNanny.DB.connect() first.");
			}
		} catch(Exception e) {
			throw new DBException("An unknown error occurred while testing database connection.");
		}
		// TODO: Register sensor into hardware table
		// return the sensor ID number
		int id = -1;
		try {
			sensorCall.setString(1,sensorID);
			sensorCall.execute();
			id = sensorCall.getInt(2);
			sensorIDs.add(id);
		
		} catch (SQLException e) {
			throw new DBException("Error registering sensor: "+e);
		}
		
		return id;
	}
	/**
	 * Establishes a connection to the database. Must be called before SendData can
	 * be used.
	 * @param databaseAddress The address to the database including database name.  Should normally be <b>cs340.pugetsound.edu/RoomNanny</b>
	 * @param debug If set to true, the data pushed will be thrown in a separate database used for debugging purposes.  This debug database may have fake and/or inconsistent data. Use when testing your application.
	 * @throws DBException If a database connection error occurs.
	 */
	public static void connect(String databaseAddress, boolean debug) throws DBException {
		
		debugMode = debug;
		
		if(debugMode) {
			// Append "Debug" on the end of the database name for debug access
			databaseAddress += "Debug";
		}
		
		// Attach protocol
		databaseAddress = "jdbc:mysql://" + databaseAddress;
		
		// Attempt to load MySQL JDBC driver
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch(Exception e) {
			throw new DBException("Error loading MySQL JDBC driver. Is the mysql-connector-java-5.1.13-bin.jar file in the classpath?");
		}
		
		// Attempt to connect to the MySQL server
		try {
			connection = DriverManager.getConnection(databaseAddress, USERNAME, PASSWORD);
		} catch(Exception e) {
			throw new DBException("Error connecting to database: "+e.toString());
		}
		
		// Double check database connection
		try {
			if(!connection.isClosed()) {
				System.out.println("Successfully connected to MySQL server");
			} else {
				throw new DBException("Error connecting to Room Nanny MySQL server. Is the server down?");
			}
		} catch(Exception e) {
			throw new DBException("Error testing database connection.");
		}
		
		// Prepare callable statements
		try {
			// Link to the call
			sensorCall = connection.prepareCall("{CALL registerSensor(?,?)}");
			// Register the output parameter
			sensorCall.registerOutParameter(2, Types.INTEGER);
			// Link to pushData call
			dataCall = connection.prepareCall("{CALL pushState(?,?,?,?)}");
		} catch (SQLException e) {
			throw new DBException("SQL error while preparing calls: "+e);
		}
		
		// New set of sensor IDs

	}
	
	/**
	 * Sends the data provided in the parameters to be stored in the
	 * database. An active database connection must be established prior to
	 * using this method. Use connect() to connect to the database.
	 * Also, the sensorID must be registered previously
	 * using the registerSensor method.  
	 * 
	 * @param sensorID ID number assigned from the registerSensor method
	 * @param temperature The current temperature.
	 * @param doorState The current state of the door.
	 * @param lightOn The current light state.
	 * @throws DBException If there is an error transmitting data.
	 * @see DB#connect(String, boolean)
	 * @see DB#registerSensor(String)
	 */
	public static void pushData(int sensorID, float temperature, DoorState doorState, boolean lightOn) throws DBException {
		
		// First, ensure the connection is active
		try {
			if(connection == null || connection.isClosed()) {
				throw new DBException("Not connected to Room Nanny database. Please call RoomNanny.DB.connect() first.");
			}
		} catch(Exception e) {
			throw new DBException("An unknown error occurred while testing database connection.");
		}
		
		// Ensure the sensor is registered
		if(!sensorIDs.contains(sensorID))
		{
			throw new DBException("Error: The sensor ID is not registered, please register the sensor using registerSensor");
		}
		
		// NOTE: MySQL will automagically round off our temperature float the tenth degree!
		// Life is sweet.
		try {
			// NOTE: Parameter index is NOT zero based
			dataCall.setInt(1, sensorID);
			dataCall.setFloat(2, temperature);
			dataCall.setString(3, doorStateMapping.get(doorState));
			dataCall.setString(4, lightStateMapping.get(lightOn));
			dataCall.execute();
			System.out.println("Data successfully stored in database");
		} catch (SQLException e) {
			throw new DBException("SQL error:  "+e.toString());
		}
		
		
	}
	
	/**
	 * Closes the active connection to the database.
	 * 
	 * @throws DBException If the database connection is already closed.
	 */
	public static void disconnect() throws DBException {
		
		// Check if the database connection is already closed.
		try {
			if(connection == null || connection.isClosed()) {
				throw new DBException("Already disconnected from Room Nanny database.");
			}
		} catch(Exception e) {
			throw new DBException("An unknown error occurred while testing database connection.");
		}
		
		// Attempt to close connection
		try {
			connection.close();
			System.out.println("Disconnected from MySQL server");
		} catch(Exception e) {
			throw new DBException("An error occurred while disconnecting from Room Nanny database:\n"+e.toString());
		}
	}


}
